﻿using DBCook.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Newtonsoft.Json;
using DBCook.Utils;
using DBCook.Common;
using DBUtil.Provider.SqlServer;
using DBUtil.Provider.SqlServer.MetaData;
using DBUtil.MetaData;
using Microsoft.AspNetCore.Hosting;
using System.IO;
using DBUtil;
using DotNetCommon.Data;

namespace DBCook.Services
{
    public class ManageService
    {
        private readonly ILogger<ManageService> logger;
        private readonly ISession session;
        private readonly IWebHostEnvironment webHostEnvironment;

        public ManageService(ILogger<ManageService> logger, ISession session, IWebHostEnvironment webHostEnvironment)
        {
            this.logger = logger;
            this.session = session;
            this.webHostEnvironment = webHostEnvironment;
        }

        public Result<bool> EntryDb(DbInfo dbInfo)
        {
            session.Set(Consts.CURRENT_DB, dbInfo);
            return Result.Ok(true);
        }

        public Result<List<SqlServerTable>> GetAllTables(string schema, string table)
        {
            var db = session.GetDb() as SqlServerAccess;
            var tables = db.Manage.ShowTables(schema).Select(table => table as SqlServerTable).ToList();
            if (!string.IsNullOrWhiteSpace(schema))
            {
                tables = tables.Where(t => t.SchemaName.IndexOf(schema, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
            }
            if (!string.IsNullOrWhiteSpace(table))
            {
                tables = tables.Where(t => t.Name.IndexOf(table, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
            }
            tables = tables.OrderBy(tbl => tbl.SchemaName + "." + tbl.Name).ToList();
            return Result.Ok(tables);
        }

        public Result<List<SqlServerProcedure>> GetAllProcs(string schema, string proc)
        {
            var db = session.GetDb() as SqlServerAccess;
            var tables = db.Manage.ShowProcedures().Select(proc => proc as SqlServerProcedure).ToList();
            if (!string.IsNullOrWhiteSpace(schema))
            {
                tables = tables.Where(t => t.SchemaName.IndexOf(schema, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
            }
            if (!string.IsNullOrWhiteSpace(proc))
            {
                tables = tables.Where(t => t.Name.IndexOf(proc, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
            }
            tables = tables.OrderBy(tbl => tbl.SchemaName + "." + tbl.Name).ToList();
            return Result.Ok(tables);
        }

        public Result<List<SqlServerFunction>> GetAllFuncs(string schema, string func)
        {
            var db = session.GetDb() as SqlServerAccess;
            var tables = db.Manage.ShowFunctions().Select(proc => proc as SqlServerFunction).ToList();
            if (!string.IsNullOrWhiteSpace(schema))
            {
                tables = tables.Where(t => t.SchemaName.IndexOf(schema, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
            }
            if (!string.IsNullOrWhiteSpace(func))
            {
                tables = tables.Where(t => t.Name.IndexOf(func, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
            }
            tables = tables.OrderBy(tbl => tbl.SchemaName + "." + tbl.Name).ToList();
            return Result.Ok(tables);
        }

        public Result<List<SqlServerSequence>> GetAllSeqs(string schema, string seq)
        {
            var db = session.GetDb() as SqlServerAccess;
            var tables = db.Manage.ShowSequences().Select(proc => proc as SqlServerSequence).ToList();
            if (!string.IsNullOrWhiteSpace(schema))
            {
                tables = tables.Where(t => t.SchemaName.IndexOf(schema, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
            }
            if (!string.IsNullOrWhiteSpace(seq))
            {
                tables = tables.Where(t => t.Name.IndexOf(seq, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
            }
            tables = tables.OrderBy(tbl => tbl.SchemaName + "." + tbl.Name).ToList();
            return Result.Ok(tables);
        }

        public Result<List<SqlServerView>> GetAllViews(string schema, string view)
        {
            var db = session.GetDb() as SqlServerAccess;
            var views = db.Manage.ShowViews().Select(view => view as SqlServerView).ToList();
            if (!string.IsNullOrWhiteSpace(schema))
            {
                views = views.Where(t => t.SchemaName.IndexOf(schema, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
            }
            if (!string.IsNullOrWhiteSpace(view))
            {
                views = views.Where(t => t.Name.IndexOf(view, StringComparison.OrdinalIgnoreCase) >= 0).ToList();
            }
            views = views.OrderBy(view => view.SchemaName + "." + view.Name).ToList();
            return Result.Ok(views);
        }

        public Result<SqlServerTable> GetTableDetail(string schema, string table)
        {
            var db = session.GetDb();
            var tableDetail = (SqlServerTable)db.Manage.ShowTableDetail(table, schema);
            return Result.Ok(tableDetail);
        }

        public Result<SqlServerView> GetViewDetail(string schema, string view)
        {
            var db = session.GetDb();
            var viewDetail = (SqlServerView)db.Manage.ShowViewDetail(view, schema);
            return Result.Ok(viewDetail);
        }

        public ResultPage<Dictionary<string, object>> GetTableData(string schema, string table, string primaryKey, int pageIndex, int pageSize)
        {
            var db = session.GetDb();
            var sql = $"select * from [{schema}].[{table}]";
            var total = db.SelectScalar<int>($"select count(1) from [{schema}].[{table}]");
            var order = "";
            if (primaryKey.Contains(","))
            {
                order = "order by ";
                primaryKey.Split(',').ToList().ForEach(key =>
                {
                    order += $"[{key}],";
                });
                order = order.TrimEnd(',');
            }
            else
            {
                order = $"order by [{primaryKey}]";
            }
            sql = db.GetSqlForPageSize(sql, order, pageSize, pageIndex);
            var dics = db.SelectDictionaryList(sql);
            return Result.OkPage(total, dics.ToArray());
        }

        public ResultPage<Dictionary<string, object>> GetViewData(string schema, string view, string primaryKey, int pageIndex, int pageSize)
        {
            var db = session.GetDb();
            var sql = $"select * from [{schema}].[{view}]";
            var total = db.SelectScalar<int>($"select count(1) from [{schema}].[{view}]");
            var order = "";
            if (primaryKey.Contains(","))
            {
                order = "order by ";
                primaryKey.Split(',').ToList().ForEach(key =>
                {
                    order += $"[{key}],";
                });
                order = order.TrimEnd(',');
            }
            else
            {
                order = $"order by [{primaryKey}]";
            }
            sql = db.GetSqlForPageSize(sql, order, pageSize, pageIndex);
            var dics = db.SelectDictionaryList(sql);
            return Result.OkPage(total, dics.ToArray());
        }

        public Result<string> AddConnect(DbInfo dbInfo)
        {
            if (string.IsNullOrWhiteSpace(dbInfo.Guid))
            {
                dbInfo.Guid = Guid.NewGuid().ToString().Replace("-", "").ToLower();
            }
            var path = Path.Combine(webHostEnvironment.WebRootPath, "config", "connections.json");
            var json = File.ReadAllText(path);
            var connections = Newtonsoft.Json.JsonConvert.DeserializeObject<List<DbInfo>>(json);
            connections.Add(dbInfo);
            json = Newtonsoft.Json.JsonConvert.SerializeObject(connections, Formatting.Indented);
            lock (typeof(ManageService))
            {
                File.WriteAllText(path, json);
            }
            return Result.Ok("保存成功!").SetData(dbInfo.Guid);
        }

        public Result<string> DeleteConnect(string guid)
        {
            var path = Path.Combine(webHostEnvironment.WebRootPath, "config", "connections.json");
            var json = File.ReadAllText(path);
            var connections = Newtonsoft.Json.JsonConvert.DeserializeObject<List<DbInfo>>(json);
            var conn = connections.FirstOrDefault(db => db.Guid == guid);
            connections.Remove(conn);
            json = Newtonsoft.Json.JsonConvert.SerializeObject(connections, Formatting.Indented);
            lock (typeof(ManageService))
            {
                File.WriteAllText(path, json);
            }
            return Result.Ok("删除成功!");
        }

        public Result<string> TestConnect(DbInfo dbInfo)
        {
            Result res = null;
            DBAccess db = null;
            if (dbInfo.Type == "MySql")
            {
                db = DBFactory.CreateDB(dbInfo.Type, $"Data Source={dbInfo.Addr};Initial Catalog={dbInfo.Db};User ID={dbInfo.User};Password={dbInfo.Pwd};");
            }
            else if (dbInfo.Type == "SqlServer")
            {
                db = DBFactory.CreateDB(dbInfo.Type, $"Data Source={dbInfo.Addr};Initial Catalog={dbInfo.Db};User={dbInfo.User};Password={dbInfo.Pwd};");
            }

            res = db.OpenTest();
            if (res.Success) return Result.Ok(res.Message);
            else return Result.NotOk(res.Message);
        }

        public Result<string> GetCreateTableSql(string schema, string table)
        {
            var db = session.GetDb();
            return Result.Ok(db.Manage.GenerateCreateTableSql(schema, table));
        }

        public Result<string> GetCreateProcSql(string schema, string proc)
        {
            var db = session.GetDb();
            return Result.Ok(((SqlServerManage)db.Manage).GenerateCreateProcSql(schema, proc));
        }

        public Result<string> GetCreateFuncSql(string schema, string proc)
        {
            var db = session.GetDb();
            return Result.Ok(((SqlServerManage)db.Manage).GenerateCreateFuncSql(schema, proc));
        }

        public Result<string> GetCreateViewSql(string schema, string view)
        {
            var db = session.GetDb();
            return Result.Ok(((SqlServerManage)db.Manage).GenerateCreateViewSql(schema, view));
        }

        public Result<string> GetCreateSeqSql(string schema, string seq)
        {
            var db = session.GetDb();
            return Result.Ok(((SqlServerManage)db.Manage).GenerateCreateSeqSql(schema, seq));
        }

        public Result<(long count, string sql, long total)> GetInsertTableSql(string schema, string table, int count)
        {
            var db = session.GetDb();
            return Result.Ok(db.Manage.GenerateInsertSql(schema, table, count));
        }

        public Result<(long count, string sql, long total)> GetInsertViewSql(string schema, string view, int count)
        {
            var db = session.GetDb();
            return Result.Ok(((SqlServerManage)db.Manage).GenerateInsertViewSql(schema, view, count));
        }

        public string CreateInsertFile(string schema, string table)
        {
            var db = session.GetDb();
            var dir = Path.Combine(webHostEnvironment.ContentRootPath, "export-sqls");
            var abspath = Path.Combine(dir, $"{schema}-{table}-{DateTime.Now.ToString("yyyyMMddHHmmss")}");
            var count = db.Manage.GenerateInsertSqlFile(schema, table, abspath);
            return abspath;
        }

        public string CreateInsertViewFile(string schema, string view)
        {
            var db = session.GetDb();
            var dir = Path.Combine(webHostEnvironment.ContentRootPath, "export-sqls");
            var abspath = Path.Combine(dir, $"{schema}-{view}-{DateTime.Now.ToString("yyyyMMddHHmmss")}");
            var count = ((SqlServerManage)db.Manage).GenerateInsertViewSqlFile(schema, view, abspath);
            return abspath;
        }

        public Result<string> GetTableCode(string schema, string table)
        {
            var db = session.GetDb();
            var dir = Path.Combine(webHostEnvironment.ContentRootPath, "export-codes");
            var abspath = Path.Combine(dir, $"{schema}-{table}-{DateTime.Now.ToString("yyyyMMddHHmmss")}");
            var code = db.Manage.GenerateCode(schema, table);
            return Result.Ok(code);
        }

        public Result<string> GetViewCode(string schema, string view)
        {
            var db = session.GetDb();
            var dir = Path.Combine(webHostEnvironment.ContentRootPath, "export-codes");
            var abspath = Path.Combine(dir, $"{schema}-{view}-{DateTime.Now.ToString("yyyyMMddHHmmss")}");
            var code = ((SqlServerManage)db.Manage).GenerateViewCode(schema, view);
            return Result.Ok(code);
        }

        public Result<string> GetCreateTableSqlBatch(List<Table> tables, bool includeInsert = false)
        {
            var db = session.GetDb();
            var dir = Path.Combine(webHostEnvironment.ContentRootPath, "export-sqls");
            var name = $"{db.DataBase}-batch-Table-{DateTime.Now.ToString("yyyyMMddHHmmss")}.sql";
            var abspath = Path.Combine(dir, name);
            db.Manage.GenerateCreateTableSqlFile(tables, abspath, includeInsert);
            return Result.Ok(name);
        }

        public Result<string> GetCreateViewSqlBatch(List<View> views)
        {
            var db = session.GetDb();
            var dir = Path.Combine(webHostEnvironment.ContentRootPath, "export-sqls");
            var name = $"{db.DataBase}-batch-View-{DateTime.Now.ToString("yyyyMMddHHmmss")}.sql";
            var abspath = Path.Combine(dir, name);
            ((SqlServerManage)db.Manage).GenerateCreateViewSqlFile(views, abspath);
            return Result.Ok(name);
        }

        public Result<string> GetCreateFuncSqlBatch(List<Function> funcs)
        {
            var db = session.GetDb();
            var dir = Path.Combine(webHostEnvironment.ContentRootPath, "export-sqls");
            var name = $"{db.DataBase}-batch-Func-{DateTime.Now.ToString("yyyyMMddHHmmss")}.sql";
            var abspath = Path.Combine(dir, name);
            ((SqlServerManage)db.Manage).GenerateCreateFuncSqlFile(funcs, abspath);
            return Result.Ok(name);
        }

        public Result<string> GetCreateProcSqlBatch(List<Procedure> procs)
        {
            var db = session.GetDb();
            var dir = Path.Combine(webHostEnvironment.ContentRootPath, "export-sqls");
            var name = $"{db.DataBase}-batch-Proc-{DateTime.Now.ToString("yyyyMMddHHmmss")}.sql";
            var abspath = Path.Combine(dir, name);
            ((SqlServerManage)db.Manage).GenerateCreateProcSqlFile(procs, abspath);
            return Result.Ok(name);
        }

        public Result<string> GetCreateSeqSqlBatch(List<Sequence> seqs)
        {
            var db = session.GetDb();
            var dir = Path.Combine(webHostEnvironment.ContentRootPath, "export-sqls");
            var name = $"{db.DataBase}-batch-Seq-{DateTime.Now.ToString("yyyyMMddHHmmss")}.sql";
            var abspath = Path.Combine(dir, name);
            ((SqlServerManage)db.Manage).GenerateCreateSeqSqlFile(seqs, abspath);
            return Result.Ok(name);
        }

        /// <summary>
        /// 更新表说明
        /// </summary>
        /// <param name="name"></param>
        /// <param name="schemaName"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        public Result<string> UpdateTableDescription(string name, string schemaName, string desc)
        {
            var db = session.GetDb();
            var res = (db.Manage as SqlServerManage).UpdateTableDescription($"[{schemaName}].[{name}]", desc);
            return new Result<string>()
            {
                Success = res.Success,
                Message = res.Message
            };
        }

        /// <summary>
        /// 更新视图说明
        /// </summary>
        /// <param name="name"></param>
        /// <param name="schemaName"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        public Result<string> UpdateViewDescription(string name, string schemaName, string desc)
        {
            var db = session.GetDb();
            var res = (db.Manage as SqlServerManage).UpdateViewDescription($"[{schemaName}].[{name}]", desc);
            return new Result<string>()
            {
                Success = res.Success,
                Message = res.Message
            };
        }

        /// <summary>
        /// 更新函数说明
        /// </summary>
        /// <param name="name"></param>
        /// <param name="schemaName"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        public Result<string> UpdateFuncDescription(string name, string schemaName, string desc)
        {
            var db = session.GetDb();
            var res = (db.Manage as SqlServerManage).UpdateFuncDescription($"[{schemaName}].[{name}]", desc);
            return new Result<string>()
            {
                Success = res.Success,
                Message = res.Message
            };
        }

        /// <summary>
        /// 更新存储过程说明
        /// </summary>
        /// <param name="name"></param>
        /// <param name="schemaName"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        public Result<string> UpdateProcDescription(string name, string schemaName, string desc)
        {
            var db = session.GetDb();
            var res = (db.Manage as SqlServerManage).UpdateProcDescription($"[{schemaName}].[{name}]", desc);
            return new Result<string>()
            {
                Success = res.Success,
                Message = res.Message
            };
        }


        public Result<string> UpdateColumnDescription(string name, string schemaName, string tableName, string desc)
        {
            var db = session.GetDb();
            var res = (db.Manage as SqlServerManage).UpdateColumnDescription($"[{schemaName}].[{tableName}]", name, desc);
            return new Result<string>()
            {
                Success = res.Success,
                Message = res.Message
            };
        }

        public Result<string> UpdateViewColumnDescription(string name, string schemaName, string viewName, string desc)
        {
            var db = session.GetDb();
            var res = (db.Manage as SqlServerManage).UpdateViewColumnDescription($"[{schemaName}].[{viewName}]", name, desc);
            return new Result<string>()
            {
                Success = res.Success,
                Message = res.Message
            };
        }
    }
}
